﻿using System;
using System.Data;
using System.Data.SqlClient;


namespace ORM
{
    public sealed class DbHelper : IDisposable
    {
        private string _connstring = "Data Source=.;Initial Catalog=taoka;Persist Security Info=True;User ID=sa;";
        private int _timeout = 60;

        internal DbHelper()
        {
            XmlHelper helper = new XmlHelper();
            _connstring = helper.GetConnstringByAilas("");
        }

        internal DbHelper(string dbalias)
        {
            XmlHelper helper = new XmlHelper();
            _connstring = helper.GetConnstringByAilas(dbalias);
        }

        internal int ExecuteNoQuery(string sqlorprocname, SqlParameter[] paras, ActionType action, ref string outParas)
        {
            int iResult = 0;
            SqlConnection conn = null;
            try
            {
                using (conn = new SqlConnection(_connstring))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    switch (action)
                    {
                        case ActionType.StoredProcedure:
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = sqlorprocname;

                            break;
                        case ActionType.SqlText:
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sqlorprocname;
                            break;
                        default:
                            cmd.CommandText = sqlorprocname;
                            cmd.CommandType = CommandType.StoredProcedure;
                            break;
                    }

                    cmd.CommandTimeout = _timeout;
                    if(null != paras && paras.Length > 0)
                        cmd.Parameters.AddRange(paras);

                    iResult = cmd.ExecuteNonQuery();
                    if (null != paras && paras.Length > 0)
                    {
                        foreach (SqlParameter sqlParameter in paras)
                        {
                            if (sqlParameter.Direction == ParameterDirection.Output)
                                outParas += "," + sqlParameter.Value;
                        }
                        outParas = outParas.Trim(',');                       
                    }
                    cmd.Dispose();
                }
            }

            catch (Exception ex)
            {
                outParas = ex.Message;
            }
            finally
            {
                if (null != conn && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return iResult;
        }

        internal int ExecuteScalar(string sqlorprocname, SqlParameter[] paras, ActionType action, ref string scalarResult, ref string outParas)
        {
            int iResult = 0;
            SqlConnection conn = null;
            try
            {
                using (conn = new SqlConnection(_connstring))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();

                    switch (action)
                    {
                        case ActionType.StoredProcedure:
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = sqlorprocname;
                            break;
                        case ActionType.SqlText:
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sqlorprocname;
                            break;
                        default:
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = sqlorprocname;
                            break;
                    }
                    cmd.CommandTimeout = 30;
                    if(null != paras && paras.Length > 0)
                        cmd.Parameters.AddRange(paras);

                    scalarResult = cmd.ExecuteScalar().ToString();
                    if (null != paras && paras.Length > 0)
                    {
                        foreach (SqlParameter sqlParameter in paras)
                        {
                            if (sqlParameter.Direction == ParameterDirection.Output)
                                outParas += "," + sqlParameter.Value;
                        }
                        outParas = outParas.Trim(',');
                    }
                    cmd.Dispose();
                    iResult = 1;
                }
            }
            catch (Exception ex)
            {
                iResult = -1;
                outParas = ex.Message;
            }
            finally
            {
                if (null != conn && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return iResult;
        
        }

        internal int ExecuteQuery(string procName, SqlParameter[] paras, ref DataTable dt, ref string outParas)
        {
            int iResult = 0;
            DataSet ds = new DataSet();
            iResult = ExecuteQuery(procName, paras, ActionType.StoredProcedure, ref ds, ref outParas);
            if(iResult == 1 && ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
                ds.Dispose();
            }            
            
            return iResult;
        }


        internal int ExecuteQuery(string sqlorprocname, SqlParameter[] paras, ActionType action, ref DataSet ds, ref string outParas)
        {
            int iResult = 0;
            SqlConnection conn = null;
            try
            {
                using (conn = new SqlConnection(_connstring))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    switch (action)
                    {
                        case ActionType.StoredProcedure:
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = sqlorprocname;

                            break;
                        case ActionType.SqlText:
                            cmd.CommandType = CommandType.Text;
                            cmd.CommandText = sqlorprocname;
                            break;
                        default:
                            cmd.CommandText = sqlorprocname;
                            cmd.CommandType = CommandType.StoredProcedure;
                            break;
                    }


                    cmd.CommandTimeout = 30;
                    if(null != paras && paras.Length > 0)
                        cmd.Parameters.AddRange(paras);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(ds);
                    
                    if (null != paras && paras.Length > 0)
                    {
                        foreach (SqlParameter sqlParameter in paras)
                        {
                            if (sqlParameter.Direction == ParameterDirection.Output)
                                outParas += "," + sqlParameter.Value;
                        }
                        outParas = outParas.Trim(',');                        
                    }

                    cmd.Dispose();
                    iResult = 1;
                }
            }
            catch (Exception ex)
            {
                iResult = -1;
                outParas = ex.Message;
            }
            finally
            {
                if (null != conn && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return iResult;
        }

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }
    }

    [Flags]
    internal enum ActionType
    {
        StoredProcedure = 0x0001,
        SqlText = 0x0002
    } 
}
